from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, desc, asc, and_, or_, update, case
from module_admin.entity.do.business.wlkts_do import Wlkts
from module_admin.entity.vo.business.wlkts_vo import WlktsPageQueryModel, WlktsModel
from utils.page_util import PageUtil
from datetime import datetime, timedelta


class WlktsDao:
    """
    往来款提示模块数据库操作层
    """

    @classmethod
    async def get_wlkts_list_dao(cls, db: AsyncSession, query_object: WlktsPageQueryModel, data_scope_sql: str,
                                 is_page: bool = False):
        """
        获取往来款提示信息列表
        :param db: 数据库连接对象
        :param query_object: 查询对象
        :param data_scope_sql: 数据权限sql
        :param is_page: 是否分页
        :return: 往来款提示信息列表
        """
        current_time = datetime.now()
        query = select(Wlkts).where(Wlkts.del_flag == '0', eval(data_scope_sql))

        # 动态生成查询语句
        if query_object.type:
            query = query.where(Wlkts.type == query_object.type)

        if query_object.duty_dept:
            query = query.where(Wlkts.duty_dept == query_object.duty_dept)

        if query_object.begin_time:
            query = query.where(Wlkts.end_time >= query_object.begin_time)

        if query_object.end_time:
            query = query.where(Wlkts.end_time <= query_object.end_time)

        query = query.order_by(
                case(
                    (Wlkts.complete_time != None, 2),  # 已完成
                    (Wlkts.end_time < current_time, 0),  # 逾期
                    (Wlkts.end_time >= current_time, 1),  # 未到期
                    else_=3
                ),
                Wlkts.end_time
            ).distinct()

        wlkts_list = await PageUtil.paginate(db, query, query_object.page_num, query_object.page_size, is_page)

        return wlkts_list



    @classmethod
    async def get_wlkts_by_title_dao(cls, db: AsyncSession, query_object: WlktsPageQueryModel, data_scope_sql: str,
                                     is_page: bool = False):
        """
        根据名称获取往来款提示信息
        :param db: 数据库连接对象
        :param title: 名称
        :return: 往来款提示信息
        """
        query = (
            select(Wlkts)
            .where(Wlkts.title.like(f'%{query_object.title}%'), Wlkts.del_flag == '0', eval(data_scope_sql))
            .order_by(desc(Wlkts.end_time))
            .distinct()
        )

        wlkts_list = await PageUtil.paginate(db, query, query_object.page_num, query_object.page_size, is_page)

        return wlkts_list

    @classmethod
    async def add_wlkts_dao(cls, db: AsyncSession, wlkts: WlktsModel):
        """
        新增往来款提示信息
        :param db: 数据库连接对象
        :param wlkts: 新增对象
        :return: 新增结果
        """
        db_wlkts = Wlkts(**wlkts.model_dump())
        db.add(db_wlkts)
        await db.flush()

        return db_wlkts


    @classmethod
    async def edit_wlkts_dao(cls, db: AsyncSession, wlkts: WlktsModel):
        """
        修改往来款提示数据库操作
        :param db: 数据库连接对象
        :param wlkts: 修改对象
        :return: 修改结果
        """
        await db.execute(update(Wlkts).where(Wlkts.id == wlkts.id).values(
            title=wlkts.title,
            type=wlkts.type,
            amount=wlkts.amount,
            party_b=wlkts.party_b,
            project_name=wlkts.project_name,
            project_cycle=wlkts.project_cycle,
            duty_dept=wlkts.duty_dept,
            duty_dept_name=wlkts.duty_dept_name,
            operator=wlkts.operator,
            phone=wlkts.phone,
            end_time=wlkts.end_time,
            complete_time=wlkts.complete_time,
            remark=wlkts.remark,
            update_by=wlkts.update_by,
            update_time=wlkts.update_time
        ))


    @classmethod
    async def delete_wlkts_dao(cls, db: AsyncSession, id: int, user_name: str):
        """
        删除往来款提示信息
        :param db: 数据库连接对象
        :param id: 主键
        :param user_name: 用户名称
        :return: 删除结果
        """
        await db.execute(update(Wlkts).where(Wlkts.id == id).values(
            del_flag='2',
            update_by=user_name,
            update_time=datetime.now()
        ))


    @classmethod
    async def check_three_months_end_dao(cls, db: AsyncSession, days: int = 90):
        """
        检查剩余days天到期的往来款提示信息
        :param db: 数据库连接对象
        :return: 往来款提示信息
        """
        query = (
            select(Wlkts)
            .where(Wlkts.end_time == (datetime.now() + timedelta(days=days)).strftime('%Y-%m-%d'), Wlkts.del_flag == '0')
            .order_by(asc(Wlkts.end_time))
            .distinct()
        )

        wlkts_list = await PageUtil.paginate(db, query, 10, 1)

        return wlkts_list
